/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.seminariosab.daos;

import com.mysql.jdbc.Statement;
import com.seminariosab.entys.SABCallsEnty;
import com.seminariosab.entys.SABRepresentanteEnty;
import com.seminariosab.tools.SABCnxSyb;
import com.seminariosab.tools.SABCnxTool;
import com.seminariosab.tools.SABUtil;
import com.sybase.jdbc4.jdbc.*;
import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Administrador
 */
public class SABEntyDao {
    private Statement SABSt;
    private ResultSet SABrs; 
    
    
    public SABEntyDao(){
        super();
       /* try {            
            //SABSt = (Statement) (new SABCnxTool().getMySQLCnx()).createStatement();
            //SABSt = (Statement) (new SABCnxTool().getMySQLCnx()).createStatement();
        } catch (SQLException ex) {
           // Logger.getLogger(SABEntyDao.class.getName()).log(Level.SEVERE, null, ex);
        }*/
    }
    
    public SABRepresentanteEnty getRepresentante(String uname, String pcode){
        SABRepresentanteEnty enty=null;
        String sqlQuery="";
        if(uname!=null && pcode!=null){
            try {
                sqlQuery="select cod_reg, cod_operador, anexo, user, pwd, super_user, fg_activo, desc_operador, nickname FROM anexos_operador where user='"+uname+"' and pwd='"+pcode+"';";
                SABrs= SABSt.executeQuery(sqlQuery);
                enty=new SABRepresentanteEnty();
                while(SABrs.next()){
                    enty.setCod_reg(SABrs.getString(1));
                    enty.setCod_operador(SABrs.getString(2));
                    enty.setAnexo(SABrs.getString(3));
                    enty.setUser(SABrs.getString(4));
                    //enty.setPwd(SABrs.getString(5));
                    enty.setSuper_user(SABrs.getString(6));
                    enty.setFg_activo(SABrs.getString(7));
                    enty.setDesc_operador(SABrs.getString(8));
                    enty.setNickname(SABrs.getString(9));
                }
                SABrs.close();
            } catch (SQLException ex) {
                Logger.getLogger(SABEntyDao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }else{
            sqlQuery="";
        }
        
        
        return enty;
    }
    
    
    /**
     * 
     * @param uname
     * @param pcode
     * @return 
     */
     public Collection<SABCallsEnty> getCallsByRepresentante(String cia, String operador, String fchal){
        SABCallsEnty enty=null;
        String sqlQuery="";
        String dir="/var/spool/asterisk/monitor/2012/130/";
        Collection<SABCallsEnty> lstEntys=null;
        Collection<File> lstFiles=null;
        if(cia !=null && operador !=null){
            try {
               lstFiles=new SABUtil().listFiles(dir);
            } catch (Exception ex) {
                Logger.getLogger(SABEntyDao.class.getName()).log(Level.SEVERE, null, ex);
                System.out.println((SABEntyDao.class.getName())+" : "+ex.toString());
            }
        }else{
            sqlQuery="";
        }
        if(lstFiles.size()>0 && lstFiles != null){
            lstEntys=new ArrayList<SABCallsEnty>(); //(java.util.List<java.io.File>)
            for (File file : lstFiles) {
                String[] structure= new SABUtil().buildGrabationStructure(file.getName());
                String[] bodystr=new SABUtil().buildGrabationStructurePart2(file.getAbsolutePath(),file.getName());
                // {e100} {14272975} {22112012} {101603.gsm}
                if(structure.length>0){
                    enty=new SABCallsEnty();
                    enty.setAnexo(structure[0]);
                    enty.setTelefono(structure[1]);
                    enty.setFecha_operacion(structure[2]);  
                    enty.setSetHoraOperacion(structure[3]);
                    // rest of the Enty class
                    enty.setNombrearchivo(file.getName());//(bodystr[4]);
                    enty.setUrl(bodystr[5]);
                }
                lstEntys.add(enty);
                enty=null;
            }
        }
        
        return lstEntys;
    }
     
/**
     * getOrdenesUpToByCodElex
     * @param gestor
     * @param descli
     * @return
     * @throws Exception 
     */ 
    public Vector<String> getOrdenesUpToByCodElex(String codelex,String fch_upto)throws Exception{		
		    Vector<String> lstClientes= new Vector<String>();
	        SABCallsEnty  sabObjCWeb = null;
	        String SABSqlQuery =null;
                SABSqlQuery = "{CALL dba.usp88_getOrdenesUpToByCodElex(?,?)};";
	        SybCallableStatement cs = null;
                codelex=codelex.trim();
                fch_upto=fch_upto.trim();
	        if(codelex != null && fch_upto != null)
	        {	
	            cs = (SybCallableStatement) new SABCnxSyb().getSybCnx(1).prepareCall(SABSqlQuery);
	            cs.setString(1, codelex);
	            cs.setString(2, fch_upto);
	            SABrs = (SybResultSet) cs.executeQuery();
	            while(SABrs != null && SABrs.next()) 
	            {
	                sabObjCWeb = new SABCallsEnty();	                
	                sabObjCWeb.setCliente(SABrs.getString(1));
	                sabObjCWeb.setDescripcion(SABrs.getString(3).toUpperCase());
	                lstClientes.addElement(""+sabObjCWeb.getCliente()+" - "+sabObjCWeb.getDescripcion());
	            }
	            SABrs.close();
	        }
	        
	        return lstClientes;
	}     
     
    /**
     * getClientesByGestor
     * @param gestor
     * @param descli
     * @return
     * @throws Exception 
     */ 
    public Vector<String> getClientesByGestor(String gestor,String descli)throws Exception{		
		    Vector<String> lstClientes= new Vector<String>();
	        SABCallsEnty  sabObjCWeb = null;
	        String SABSqlQuery = "{CALL dba.usp_web_getClientesByGestor(?,?)}";
	        SybCallableStatement cs = null;
	        if(gestor.trim() != null && descli.trim() != null)
	        {
	        	
	            cs = (SybCallableStatement) new SABCnxSyb().getSybCnx(1).prepareCall(SABSqlQuery);
	            cs.setString(1, gestor);
	            cs.setString(2, descli);
	            SABrs = (SybResultSet) cs.executeQuery();
	            while(SABrs != null && SABrs.next())
	            {
	                sabObjCWeb = new SABCallsEnty();	                
	                sabObjCWeb.setCliente(SABrs.getString(1));
	                sabObjCWeb.setDescripcion(SABrs.getString(3).toUpperCase());
	                lstClientes.addElement(""+sabObjCWeb.getCliente()+" - "+sabObjCWeb.getDescripcion());
	            }
	            SABrs.close();
	        }
	        
	        return lstClientes;
	}
    
    
    /**
     * 
     * @param gestor
     * @param descli
     * @return 
     */
    public List<String> getCliByRepresentante(String gestor, String descli){
        List<String> lstCli=null;
        SybStatement stSAB=null;
        SybResultSet rsSAB=null;
        String sqlQry="";
        sqlQry="SELECT clientes.cosabcli, clientes.cobvlcli, (dba.fn_getnombrecliente(clientes.cosabcli,'A')) as  dbs_descli, clientes.estado AS estado, isNull(clientes_bloqueos.tipo, 'ok') AS as_tiblq,(case as_tiblq when 'ok' then "+
					"(case estado  when '0' then 'FICHA ACTIVA'"+
									 " when '2' then 'FICHA ANTIGUA' "+
									 " when '3' then 'FICHA ANTIGUA' "+
									 " when '8' then 'FICHA ANTIGUA' "+
									  " when '7' then 'FICHA ACTIVA' "+
									  " when '9' then 'BLOQUEADO' else space(0) end) "+
			" else	'Bloqueada: ' + (SELECT tabgen.destag 	from dba.tabgen where tabgen.tabla = 'TIBLQCLI' AND "+
" tabgen.codtag = clientes_bloqueos.tiblqcli )  end) 	as des_tipo    FROM dba.clientes_bloqueos RIGHT OUTER JOIN dba.clientes ON clientes_bloqueos.cosabcli = clientes.cosabcli"+
   " WHERE ( clientes.gestor = '"+gestor+"' ) AND  ( dbs_descli like '"+descli+"%' )   "+
" ORDER BY clientes.descli ASC;";
        System.out.println(""+sqlQry);
        try {
            System.out.println("ingresando a SybConnect ");
            SybConnection c=new SABCnxSyb().getSybCnx(1);
            System.out.println("dentro de SybConnect: "+c.getMetaData().getUserName());
            stSAB=(SybStatement) (c).createStatement();
            System.out.println("dentro de statement");
            rsSAB=(SybResultSet) stSAB.executeQuery(sqlQry);
            System.out.println("dentro query");
            lstCli=new ArrayList<String>();
            while (rsSAB.next()) {
                System.out.println("ingresando a  rs.get(): ");
                lstCli.add(rsSAB.getString(1)+";"+rsSAB.getString(2)+";"+rsSAB.getString(3)+";"+rsSAB.getString(4)+";"+rsSAB.getString(5)+";"+rsSAB.getString(6));
                System.out.println("Saliendo de  rs.get(): ");
            }
        } catch (Exception e) {
            e.toString();
        }
        return lstCli;
                
    }
    
   
}
